package com.agency8x.model;

import java.text.SimpleDateFormat;
import java.util.Date;

import com.agency8x.model.base.BaseAgencyModel;
import com.agency8x.sqls.AgencySqls;
import com.agency8x.sqls.WithdrawSqls;
import com.agency8x.util.DateTimeUtil;
import com.agency8x.util.RegexUtil;
import com.jfinal.kit.StrKit;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Page;

/**
 * Agency model. 数据库字段名建议使用驼峰命名规则，便于与 java 代码保持一致，如字段名： userId
 */
@SuppressWarnings("serial")
public class AgencyModel extends BaseAgencyModel<AgencyModel> {

	public static final AgencyModel me = new AgencyModel().dao();
	SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");

	/**
	 * 所有 sql 与业务逻辑写在 Service 中，在此放在 Model 中仅为示例
	 */
	public Page<AgencyModel> paginate(int pageNumber, int pageSize, String word, String date1, String date2,
			Integer flag) {
		String sql = AgencySqls.me.getPagingSelectSql();
		String sqlFrom = AgencySqls.me.getPagingExceptSelectSql();
		switch (flag) {
		case 1: // 今日新增
			sqlFrom = sqlFrom + " and a.regist_date = CONVERT(varchar(100), '" + formatter.format(new Date())
					+ " 23:59:59', 23)";
			break;
		case 2: // 本周新增
			sqlFrom = sqlFrom + " and datediff(week,regist_date,getdate())=0 ";
			break;
		case 3: // 30天未登录
			sqlFrom = sqlFrom + " and a.login_time <= CONVERT(varchar(100),dateadd(day,-30,'"
					+ formatter.format(new Date()) + " 00:00:00'),23)";
			break;
		}
        if (StrKit.notBlank(date1) && DateTimeUtil.validateDate(date1)) {
        	sqlFrom += " and a.regist_time >='" + date1 + " 0:0:0'";
        }
        if (StrKit.notBlank(date2) && DateTimeUtil.validateDate(date2)) {
        	sqlFrom += " and a.regist_time <='" + date2 + " 23:59:59'";
        }

		if (StrKit.notBlank(word)) {
			if (RegexUtil.isNumeric(word))
				sqlFrom = sqlFrom + " and a.user_id like '%" + word + "%'";
			else
				sqlFrom = sqlFrom + " and (a.user_name like '%" + word + "%' or a.nickname like '%" + word + "%')";
		}
		sqlFrom += " order by a.agent_id desc ";
		return paginate(pageNumber, pageSize, sql, sqlFrom);
	}

	/**
	 * 获取下级代理
	 */
	public Page<AgencyModel> parent1inate(int pageNumber, int pageSize, Integer parent1_uid, String where,String date1, String date2,
			Integer daili) {
		String sql = "select a.user_id,a.nickname,a.user_status,b.child1_agent_count ,c.blance,d.income_amount_today,d.income_amount_month,"
				+ "d.income_amount_year,d.withdraw_amount_total,d.recharge_amount_total,d.income_amount_total,d.withdraw_amount_today,d.recharge_amount_today ,a.top_agent_id ";
		String sqlFrom = "from t_user a left join t_user_agent_statistics b on a.user_id =b.user_id "
				+ "left join t_user_finance_account c on a.user_id =c.user_id "
				+ "left join t_user_finance_statistics d on a.user_id = d.user_id " + "where a.is_agent = 1 ";
		if (parent1_uid > 0 && daili == 1) {// 一级代理
			sqlFrom = sqlFrom + " and a.parent1_uid=" + parent1_uid;
		}
		if (parent1_uid > 0 && daili == 2) {// 二级代理
			sqlFrom = sqlFrom + " and a.parent2_uid=" + parent1_uid;
		}
		/*------------------------------------搜索----------------------------------------------*/
		if (StrKit.notBlank(date1) && DateTimeUtil.validateDate(date1)) {
        	sqlFrom += " and a.regist_time >='" + date1 + " 0:0:0'";
        }
        if (StrKit.notBlank(date2) && DateTimeUtil.validateDate(date2)) {
        	sqlFrom += " and a.regist_time <='" + date2 + " 23:59:59'";
        }
		if (StrKit.notBlank(where) && !"null".equals(where)) {
			if (RegexUtil.isNumeric(where))
				sqlFrom = sqlFrom + " and a.user_id=" + where;
			else
				sqlFrom = sqlFrom + " and (a.user_name like '%" + where + "%' or a.nickname like '%" + where + "%')";

		}

		return paginate(pageNumber, pageSize, sql, sqlFrom);
	}

	public Page<AgencyModel> getAgent1Pagination(int pageNumber, int pageSize, Integer parent1_uid, String where,String date1, String date2,
			Integer daili) {
		String sql = "select a.user_id,a.nickname,a.user_status,b.child1_agent_count ,c.blance,d.income_amount_today,d.income_amount_month,"
				+ "d.income_amount_year,d.withdraw_amount_total,d.recharge_amount_total,d.income_amount_total,d.withdraw_amount_today,d.recharge_amount_today ,a.top_agent_id ";
		String sqlFrom = "from t_user a left join t_user_agent_statistics b on a.user_id =b.user_id "
				+ "left join t_user_finance_account c on a.user_id =c.user_id "
				+ "left join t_user_finance_statistics d on a.user_id = d.user_id ";
		sqlFrom = sqlFrom+" where a.is_agent = 1 ";
		if (parent1_uid > 0 && daili == 1) {// 一级代理
			sqlFrom = sqlFrom + " and a.parent1_uid=" + parent1_uid;
		}
		if (parent1_uid > 0 && daili == 2) {// 二级代理
			sqlFrom = sqlFrom + " and a.parent2_uid=" + parent1_uid;
		}
		/*------------------------------------搜索----------------------------------------------*/
		if (StrKit.notBlank(date1) && DateTimeUtil.validateDate(date1)) {
        	sqlFrom += " and a.regist_time >='" + date1 + " 0:0:0'";
        }
        if (StrKit.notBlank(date2) && DateTimeUtil.validateDate(date2)) {
        	sqlFrom += " and a.regist_time <='" + date2 + " 23:59:59'";
        }
		if (StrKit.notBlank(where) && !"null".equals(where)) {
			if (RegexUtil.isNumeric(where))
				sqlFrom = sqlFrom + " and a.user_id=" + where;
			else
				sqlFrom = sqlFrom + " and (a.user_name like '%" + where + "%' or a.nickname like '%" + where + "%')";

		}

		return paginate(pageNumber, pageSize, sql, sqlFrom);
	}

	public AgencyModel agenEdit(Integer id) {
		return me.findFirst(AgencySqls.me.getFindByIdSql(), id);
	}

	/**
	 * 账户明细
	 * 
	 * @param endDate
	 * @param beginDate
	 */
	public Page<AgencyModel> accountDetails(int pageNumber, int pageSize, String parent_uid, String where,
			String beginDate, String endDate, String status) {
		String sql = "select u.user_id,f.id,f.serial_no,f.amount,f.trade_type,f.pay_channel,f.blance,f.create_time,f.remark,f.out_trade_no,f.auditor_id ";
		String sqlFrom = "from t_user u left join t_finance_transaction_details f on u.user_id=f.user_id "
				+ "where u.is_agent=1 and f.trade_type in (1,2,3) ";
		if ("1".equals(status)) {
			if (StrKit.notBlank(parent_uid))
				if (RegexUtil.isNumeric(parent_uid))
					sqlFrom = sqlFrom + " and u.parent1_uid=" + parent_uid;
		} else if ("2".equals(status)) {
			if (StrKit.notBlank(parent_uid))
				if (RegexUtil.isNumeric(parent_uid))
					sqlFrom = sqlFrom + " and u.parent2_uid=" + parent_uid;
		}
		/*------------搜索--------------*/
		if (where != null && !"".equals(where) && !"null".equals(where)) {
			if (RegexUtil.isNumeric(where))
				sqlFrom = sqlFrom + " and u.user_id=" + where;
			else
				sqlFrom = sqlFrom + " and (u.user_name like '%" + where + "%' or u.nickname like '%" + where + "%')";

		}
		if (StrKit.notBlank(beginDate) && DateTimeUtil.validateDate(beginDate)) {
			sqlFrom += " and f.create_time >='" + beginDate + " 0:0:0'";
        }
        if (StrKit.notBlank(endDate) && DateTimeUtil.validateDate(endDate)) {
        	sqlFrom += " and f.create_time <='" + endDate + " 23:59:59'";
        }
		return paginate(pageNumber, pageSize, sql, sqlFrom);
	}

	/**
	 * 提现明细
	 * 
	 * @param endDate
	 * @param beginDate
	 */
	public Page<AgencyModel> withdrawalDetails(int pageNumber, int pageSize, String parent_uid, String where,
			String beginDate, String endDate, String status) {
		String sql = "select u.user_id,f.id,f.serial_no,f.amount,f.trade_type,f.pay_channel,f.blance,f.create_time,f.remark,f.out_trade_no,f.auditor_id ";
		String sqlFrom = "from t_user u left join t_finance_transaction_details f on u.user_id=f.user_id "
				+ "where u.is_agent=1 and f.trade_type = 3 ";
		if ("1".equals(status)) {
			if (StrKit.notBlank(parent_uid))
				if (RegexUtil.isNumeric(parent_uid))
					sqlFrom = sqlFrom + " and u.parent1_uid=" + parent_uid;
		} else if ("2".equals(status)) {
			if (StrKit.notBlank(parent_uid))
				if (RegexUtil.isNumeric(parent_uid))
					sqlFrom = sqlFrom + " and u.parent2_uid=" + parent_uid;
		}
		/*------------搜索--------------*/
		if (where != null && !"".equals(where) && !"null".equals(where)) {
			if (RegexUtil.isNumeric(where))
				sqlFrom = sqlFrom + " and u.user_id=" + where;
			else
				sqlFrom = sqlFrom + " and (u.user_name like '%" + where + "%' or u.nickname like '%" + where + "%')";

		}
		if (StrKit.notBlank(beginDate) && DateTimeUtil.validateDate(beginDate)) {
			sqlFrom += " and f.create_time >='" + beginDate + " 0:0:0'";
        }
        if (StrKit.notBlank(endDate) && DateTimeUtil.validateDate(endDate)) {
        	sqlFrom += " and f.create_time <='" + endDate + " 23:59:59'";
        }
		return paginate(pageNumber, pageSize, sql, sqlFrom);
	}

	/**
	 * 充值明细
	 * 
	 * @param endDate
	 * @param beginDate
	 */
	public Page<AgencyModel> topUpDetails(int pageNumber, int pageSize, String parent_uid, String where,
			String beginDate, String endDate, String status) {
		String sql = "select u.user_id,f.id,f.serial_no,f.amount,f.trade_type,f.pay_channel,f.blance,f.create_time,f.remark,f.out_trade_no,f.auditor_id ";
		String sqlFrom = "from t_user u left join t_finance_transaction_details f on u.user_id=f.user_id "
				+ "where u.is_agent=1 and f.trade_type = 1 ";
		if ("1".equals(status)) {
			if (StrKit.notBlank(parent_uid))
				if (RegexUtil.isNumeric(parent_uid))
					sqlFrom = sqlFrom + " and u.parent1_uid=" + parent_uid;
		} else if ("2".equals(status)) {
			if (StrKit.notBlank(parent_uid))
				if (RegexUtil.isNumeric(parent_uid))
					sqlFrom = sqlFrom + " and u.parent2_uid=" + parent_uid;
		}
		/*------------搜索--------------*/
		if (where != null && !"".equals(where) && !"null".equals(where)) {
			if (RegexUtil.isNumeric(where))
				sqlFrom = sqlFrom + " and u.user_id=" + where;
			else
				sqlFrom = sqlFrom + " and (u.user_name like '%" + where + "%' or u.nickname like '%" + where + "%')";

		}
		if (StrKit.notBlank(beginDate) && DateTimeUtil.validateDate(beginDate)) {
			sqlFrom += " and f.create_time >='" + beginDate + " 0:0:0'";
        }
        if (StrKit.notBlank(endDate) && DateTimeUtil.validateDate(endDate)) {
        	sqlFrom += " and f.create_time <='" + endDate + " 23:59:59'";
        }
		return paginate(pageNumber, pageSize, sql, sqlFrom);
	}

	/**
	 * 累计收入总额
	 * 
	 * @param endDate
	 * @param beginDate
	 */
	public Page<AgencyModel> incomeDetail(int pageNumber, int pageSize, String where, String cash_date,
			String beginDate, String endDate) {
		String sql = "select u.user_id,f.id,f.serial_no,f.amount,f.trade_type,f.pay_channel,f.blance,f.create_time,f.remark,f.out_trade_no,f.auditor_id ";
		String sqlFrom = "from t_user u left join t_finance_transaction_details f on u.user_id=f.user_id "
				+ "where u.is_agent=1 and f.trade_type = 2 ";
		/*------------搜索--------------*/
		if (where != null && !"".equals(where)) {
			if (RegexUtil.isNumeric(where))
				sqlFrom = sqlFrom + " and u.user_id=" + where;
			else
				sqlFrom = sqlFrom + " and (u.user_name like '%" + where + "%' or u.nickname like '%" + where + "%')";

		}
		if (!"".equals(cash_date) && "1".equals(cash_date)) {// 获取今日信息
			sqlFrom = sqlFrom + " and f.create_time = CONVERT(varchar(100), '" + formatter.format(new Date())
					+ " 00:00:00', 23)";
		} else {
			if (StrKit.notBlank(beginDate) && DateTimeUtil.validateDate(beginDate)) {
				sqlFrom += " and f.create_time >='" + beginDate + " 0:0:0'";
	        }
	        if (StrKit.notBlank(endDate) && DateTimeUtil.validateDate(endDate)) {
	        	sqlFrom += " and f.create_time <='" + endDate + " 23:59:59'";
	        }
		}
		return paginate(pageNumber, pageSize, sql, sqlFrom);
	}

	/**
	 * 累计提现金额
	 * 
	 * @param endDate
	 * @param beginDate
	 */
	public Page<AgencyModel> withDrawaldetail(int pageNumber, int pageSize, String where, String cash_date,
			String beginDate, String endDate) {
		String sql = "select u.user_id,f.id,f.serial_no,f.amount,f.trade_type,f.pay_channel,f.blance,f.create_time,f.remark,f.out_trade_no,f.auditor_id ";
		String sqlFrom = "from t_user u left join t_finance_transaction_details f on u.user_id=f.user_id "
				+ "where u.is_agent=1 and f.trade_type = 3 ";
		/*------------搜索--------------*/
		if (where != null && !"".equals(where)) {
			if (RegexUtil.isNumeric(where))
				sqlFrom = sqlFrom + " and u.user_id=" + where;
			else
				sqlFrom = sqlFrom + " and (u.user_name like '%" + where + "%' or u.nickname like '%" + where + "%')";

		}
		if (!"".equals(cash_date) && "1".equals(cash_date)) {// 获取今日信息
			sqlFrom = sqlFrom + " and f.create_time = CONVERT(varchar(100), '" + formatter.format(new Date())
					+ " 00:00:00', 23)";
		} else {
			if (StrKit.notBlank(beginDate) && DateTimeUtil.validateDate(beginDate)) {
				sqlFrom += " and f.create_time >='" + beginDate + " 0:0:0'";
	        }
	        if (StrKit.notBlank(endDate) && DateTimeUtil.validateDate(endDate)) {
	        	sqlFrom += " and f.create_time <='" + endDate + " 23:59:59'";
	        }
		}
		return paginate(pageNumber, pageSize, sql, sqlFrom);
	}

	/**
	 * 累计充值金额
	 * 
	 * @param endDate
	 * @param beginDate
	 */
	public Page<AgencyModel> topupDetail(int pageNumber, int pageSize, String where, String cash_date, String beginDate,
			String endDate) {
		String sql = "select u.user_id,f.id,f.serial_no,f.amount,f.trade_type,f.pay_channel,f.blance,f.create_time,f.remark,f.out_trade_no,f.auditor_id ";
		String sqlFrom = "from t_user u left join t_finance_transaction_details f on u.user_id=f.user_id "
				+ "where u.is_agent=1 and f.trade_type = 1 ";
		/*------------搜索--------------*/
		if (where != null && !"".equals(where)) {
			if (RegexUtil.isNumeric(where))
				sqlFrom = sqlFrom + " and u.user_id=" + where;
			else
				sqlFrom = sqlFrom + " and (u.user_name like '%" + where + "%' or u.nickname like '%" + where + "%')";

		}
		if (!"".equals(cash_date) && "1".equals(cash_date)) {// 获取今日信息
			sqlFrom = sqlFrom + " and f.create_time = CONVERT(varchar(100), '" + formatter.format(new Date())
					+ " 00:00:00', 23)";
		} else {
			if (StrKit.notBlank(beginDate) && DateTimeUtil.validateDate(beginDate)) {
				sqlFrom += " and f.create_time >='" + beginDate + " 0:0:0'";
	        }
	        if (StrKit.notBlank(endDate) && DateTimeUtil.validateDate(endDate)) {
	        	sqlFrom += " and f.create_time <='" + endDate + " 23:59:59'";
	        }
		}
		return paginate(pageNumber, pageSize, sql, sqlFrom);
	}

	/**
	 * 待审核
	 */
	public Page<AgencyModel> paginatePending(int pageNumber, int pageSize, String user_id, String search_word,
			Integer auditor_id, Integer status) {
		String sql = WithdrawSqls.me.getPagingSelectSql();
		String sqlFrom = WithdrawSqls.me.getPagingExceptSelectSql();
		sqlFrom += "where u.is_agent=1 and w.status=0";
		if (status == 1 && status != null) {
			if (StrKit.notBlank(user_id))
				if (RegexUtil.isNumeric(user_id))
					sqlFrom = sqlFrom + " and u.parent1_uid=" + user_id;
		} else if (status == 2 && status != null) {
			if (StrKit.notBlank(user_id))
				if (RegexUtil.isNumeric(user_id))
					sqlFrom = sqlFrom + " and u.parent2_uid=" + user_id;
		}
		if (auditor_id > 0) {
			sqlFrom = sqlFrom + " and w.auditor_id = " + auditor_id;
		} else {
			sqlFrom = sqlFrom + " and w.auditor_id > 0 ";
		}
		if (RegexUtil.isNumeric(search_word) && StrKit.notBlank(search_word)) {
			sqlFrom = sqlFrom + " and w.user_id = " + search_word;
		}
		sqlFrom = sqlFrom + " order by w.id desc";
		return paginate(pageNumber, pageSize, sql, sqlFrom);
	}

	/**
	 * 审核记录
	 * 
	 * @param endDate
	 * @param beginDate
	 */
	public Page<AgencyModel> paginateHistory(int pageNumber, int pageSize, String searchword, String user_id,
			Integer auditor_uid, Integer statu, String beginDate, String endDate, Integer status) {
		String sql = WithdrawSqls.me.getPagingSelectSql();
		String sqlFrom = WithdrawSqls.me.getPagingExceptSelectSql();
		sqlFrom += "where u.is_agent=1 and w.status= " + statu;
		// if(id != 0 && id != null){//我的审核记录
		// sqlFrom = sqlFrom + " and w.auditor_id = "+id;
		// }
		if (status == 1 && status != null) {
			if (StrKit.notBlank(user_id))
				if (RegexUtil.isNumeric(user_id))
					sqlFrom = sqlFrom + " and u.parent1_uid=" + user_id;
		} else if (status == 2 && status != null) {
			if (StrKit.notBlank(user_id))
				if (RegexUtil.isNumeric(user_id))
					sqlFrom = sqlFrom + " and u.parent2_uid=" + user_id;
		}
		if (StrKit.notBlank(beginDate) && DateTimeUtil.validateDate(beginDate)) {
			sqlFrom += " and w.create_time >='" + beginDate + " 0:0:0'";
        }
        if (StrKit.notBlank(endDate) && DateTimeUtil.validateDate(endDate)) {
        	sqlFrom += " and w.create_time <='" + endDate + " 23:59:59'";
        }

		if (StrKit.notBlank(searchword)) {
			sqlFrom = sqlFrom + " and u.nickname like '%" + searchword + "%'";
		}
		sqlFrom = sqlFrom + " order by w.create_time desc";
		return paginate(pageNumber, pageSize, sql, sqlFrom);
	}

	public Page<AgencyModel> recharge(int pageNumber, int pageSize, String user_id, Integer user_type,
			String search_word, String beginDate, String endDate, Integer status) {
		String sql = "SELECT f.* ";
		String sqlFrom = " FROM t_finance_recharge f left join t_user u on  f.user_id =u.user_id where 1=1 ";

		if (user_type == 1 && user_type != null) {
			if (StrKit.notBlank(user_id))
				if (RegexUtil.isNumeric(user_id))
					sqlFrom = sqlFrom + " and u.parent1_uid=" + user_id;
		} else if (user_type == 2 && user_type != null) {
			if (StrKit.notBlank(user_id))
				if (RegexUtil.isNumeric(user_id))
					sqlFrom = sqlFrom + " and u.parent2_uid=" + user_id;
		}

		if (status != null)
			sqlFrom = sqlFrom + " and f.status=" + status;

		/*----------------------搜索--------------------------*/
		if (StrKit.notBlank(beginDate) && DateTimeUtil.validateDate(beginDate)) {
			sqlFrom += " and create_time >='" + beginDate + " 0:0:0'";
        }
        if (StrKit.notBlank(endDate) && DateTimeUtil.validateDate(endDate)) {
        	sqlFrom += " and create_time <='" + endDate + " 23:59:59'";
        }

		if (search_word != null && !"".equals(search_word.trim()) && !"null".equals(search_word)) {
			if (RegexUtil.isNumeric(search_word)) {
				sqlFrom = sqlFrom + " and user_id =" + search_word;
			}
		}
		sqlFrom = sqlFrom + " order by create_time desc";
		return paginate(pageNumber, pageSize, sql, sqlFrom);
	}

	public boolean postting(String search_word, String beginDate, String endDate) {
		if (!"null".equals(beginDate) && !"null".equals(endDate) && !"null".equals(search_word)) {
			return true;
		}
		return false;
	}

	public String getSexText() {
		Short sex = getSex();
		if (null == sex) {
			return "-";
		}
		return sex == 1 ? "男" : "女";
	}

	// 1级代理累计收入统计
	public double getAgent1IncomeAmountTotal(Integer user_id) {
		String sqlstmt = "select sum(rebate_amount_total) from t_agent1_statistics where user_id=" + user_id;
		return Db.queryDouble(sqlstmt);
	}

	// 1级代理今日收入统计
	public double getAgent1IncomeAmountToday(Integer user_id) {
		String sqlstmt = "select sum(rebate_amount_today) from t_agent1_statistics where user_id=" + user_id;
		return Db.queryDouble(sqlstmt);
	}
}
